Springboot集成sqlite数据库，并使用jpa、Hibernate操作sqlite |
您所在的位置：网站首页 › spring boot 整合jpa › Springboot集成sqlite数据库，并使用jpa、Hibernate操作sqlite |
|
之前举例使用jpa、Hibernate多是以mysql为例，这次因为需要使用一个内嵌式数据库，选择了sqlite，网上多是讲一些sqlite的api封装的框架。这里我们还是使用jpa、Hibernate来操作sqlite。 新建一个Springboot项目，pom如下： 4.0.0 com.mindata.blockchain md_blockchain 0.0.1-SNAPSHOT jar md_blockchain Demo project for Spring Boot org.springframework.boot spring-boot-starter-parent 1.5.10.RELEASE UTF-8 UTF-8 1.8 1.2.29 org.springframework.boot spring-boot-starter org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-test test com.alibaba fastjson ${fastjson.version} org.springframework.boot spring-boot-starter-data-jpa org.xerial sqlite-jdbc 3.20.0 org.apache.commons commons-dbcp2 2.1.1 org.springframework.boot spring-boot-maven-plugin要使用sqlite，需要引入sqlite-jdbc这个依赖。 然后需要配置数据源DataSource，见DataSourceConfiguration.java package com.mindata.blockchain.core.sqlite; import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.sqlite.SQLiteDataSource; import javax.sql.DataSource; /** * 配置sqlite数据库的DataSource * @author wuweifeng wrote on 2018/3/2. */ @Configuration public class DataSourceConfiguration { @Bean(destroyMethod = "", name = "EmbeddeddataSource") public DataSource dataSource() { DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(); dataSourceBuilder.driverClassName("org.sqlite.JDBC"); dataSourceBuilder.url("jdbc:sqlite:" + "example.db"); dataSourceBuilder.type(SQLiteDataSource.class); return dataSourceBuilder.build(); } }这里的配置也可以写在application.yml里，加载driverClass驱动，设置数据库名为example.db。名字可以自己设置，注意，使用时不需要手工创建example.db，它会自动创建的。 然后配置Jpa相关，JpaConfiguration.java package com.mindata.blockchain.core.sqlite; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties; import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.annotation.Resource; import javax.persistence.EntityManagerFactory; import javax.sql.DataSource; import java.util.Map; /** * @author wuweifeng wrote on 2018/3/2. */ @Configuration @EnableJpaRepositories( basePackages = "com.mindata.blockchain.core.repository", transactionManagerRef = "jpaTransactionManager", entityManagerFactoryRef = "localContainerEntityManagerFactoryBean" ) @EnableTransactionManagement public class JpaConfiguration { @Resource private JpaProperties jpaProperties; @Autowired @Bean public JpaTransactionManager jpaTransactionManager(@Qualifier(value = "EmbeddeddataSource") DataSource dataSource, EntityManagerFactory entityManagerFactory) { JpaTransactionManager jpaTransactionManager = new JpaTransactionManager(); jpaTransactionManager.setEntityManagerFactory(entityManagerFactory); jpaTransactionManager.setDataSource(dataSource); return jpaTransactionManager; } @Autowired @Bean LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean(@Qualifier(value = "EmbeddeddataSource") DataSource dataSource, EntityManagerFactoryBuilder builder) { return builder.dataSource(dataSource) .packages("com.mindata.blockchain.core.model") .properties(getVendorProperties(dataSource)) .build(); } private Map getVendorProperties(DataSource dataSource) { return jpaProperties.getHibernateProperties(dataSource); } }这个类和之前使用mysql时配置的jpa并没有什么区别，basePackages里是Repository的目录，下面构建LocalContainerEntityManagerFactoryBean时是model的目录。和配置mysql完全一样。然后Autowired JpaProperties，这个jpaProperties就是在yml配置的各属性，在debug时，可以看到里面的属性。 所以我们的application.yml是 spring: jpa: show-sql: false database-platform: com.mindata.blockchain.core.sqlite.SQLiteDialect generate-ddl: true和mysql唯一的不同就是这个database-platform，需要指明为SQLiteDialect即SQLite方言。 可以看到这个类是自定义的，spring官方没有提供这个方言。 如果你用的是Hibernate4，那么可以在这个地址https://github.com/EnigmaBridge/hibernate4-sqlite-dialect 找到Hibernate4-sqlite，直接添加依赖就好。我们使用的是Hibernate5，需要添加如下几个类，设置对应的Hibernate5对sqlite的方言。 SQLiteDialect.java package com.mindata.blockchain.core.sqlite; import com.mindata.blockchain.core.sqlite.identity.SQLiteDialectIdentityColumnSupport; import org.hibernate.JDBCException; import org.hibernate.ScrollMode; import org.hibernate.dialect.Dialect; import org.hibernate.dialect.function.*; import org.hibernate.dialect.identity.IdentityColumnSupport; import org.hibernate.dialect.pagination.AbstractLimitHandler; import org.hibernate.dialect.pagination.LimitHandler; import org.hibernate.dialect.pagination.LimitHelper; import org.hibernate.dialect.unique.DefaultUniqueDelegate; import org.hibernate.dialect.unique.UniqueDelegate; import org.hibernate.engine.spi.RowSelection; import org.hibernate.exception.DataException; import org.hibernate.exception.JDBCConnectionException; import org.hibernate.exception.LockAcquisitionException; import org.hibernate.exception.spi.SQLExceptionConversionDelegate; import org.hibernate.exception.spi.TemplatedViolatedConstraintNameExtracter; import org.hibernate.exception.spi.ViolatedConstraintNameExtracter; import org.hibernate.internal.util.JdbcExceptionHelper; import org.hibernate.mapping.Column; import org.hibernate.type.StandardBasicTypes; import java.sql.SQLException; import java.sql.Types; /** * @author wuweifeng wrote on 2018/3/2. */ public class SQLiteDialect extends Dialect { private final UniqueDelegate uniqueDelegate; public SQLiteDialect() { registerColumnType(Types.BIT, "boolean"); //registerColumnType(Types.FLOAT, "float"); //registerColumnType(Types.DOUBLE, "double"); registerColumnType(Types.DECIMAL, "decimal"); registerColumnType(Types.CHAR, "char"); registerColumnType(Types.LONGVARCHAR, "longvarchar"); registerColumnType(Types.TIMESTAMP, "datetime"); registerColumnType(Types.BINARY, "blob"); registerColumnType(Types.VARBINARY, "blob"); registerColumnType(Types.LONGVARBINARY, "blob"); registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", "")); registerFunction("mod", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 % ?2")); registerFunction("quote", new StandardSQLFunction("quote", StandardBasicTypes.STRING)); registerFunction("random", new NoArgSQLFunction("random", StandardBasicTypes.INTEGER)); registerFunction("round", new StandardSQLFunction("round")); registerFunction("substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING)); registerFunction("trim", new AbstractAnsiTrimEmulationFunction() { @Override protected SQLFunction resolveBothSpaceTrimFunction() { return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1)"); } @Override protected SQLFunction resolveBothSpaceTrimFromFunction() { return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?2)"); } @Override protected SQLFunction resolveLeadingSpaceTrimFunction() { return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1)"); } @Override protected SQLFunction resolveTrailingSpaceTrimFunction() { return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1)"); } @Override protected SQLFunction resolveBothTrimFunction() { return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1, ?2)"); } @Override protected SQLFunction resolveLeadingTrimFunction() { return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1, ?2)"); } @Override protected SQLFunction resolveTrailingTrimFunction() { return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1, ?2)"); } }); uniqueDelegate = new SQLiteUniqueDelegate(this); } // database type mapping support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ /*@Override public String getCastTypeName(int code) { // http://sqlite.org/lang_expr.html#castexpr return super.getCastTypeName( code ); }*/ // IDENTITY support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ private static final SQLiteDialectIdentityColumnSupport IDENTITY_COLUMN_SUPPORT = new SQLiteDialectIdentityColumnSupport(new SQLiteDialect()); @Override public IdentityColumnSupport getIdentityColumnSupport() { return IDENTITY_COLUMN_SUPPORT; } // limit/offset support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ private static final AbstractLimitHandler LIMIT_HANDLER = new AbstractLimitHandler() { @Override public String processSql(String sql, RowSelection selection) { final boolean hasOffset = LimitHelper.hasFirstRow(selection); return sql + (hasOffset ? " limit ? offset ?" : " limit ?"); } @Override public boolean supportsLimit() { return true; } @Override public boolean bindLimitParametersInReverseOrder() { return true; } }; @Override public LimitHandler getLimitHandler() { return LIMIT_HANDLER; } // lock acquisition support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @Override public boolean supportsLockTimeouts() { // may be http://sqlite.org/c3ref/db_mutex.html ? return false; } @Override public String getForUpdateString() { return ""; } @Override public boolean supportsOuterJoinForUpdate() { return false; } // current timestamp support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @Override public boolean supportsCurrentTimestampSelection() { return true; } @Override public boolean isCurrentTimestampSelectStringCallable() { return false; } @Override public String getCurrentTimestampSelectString() { return "select current_timestamp"; } // SQLException support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ private static final int SQLITE_BUSY = 5; private static final int SQLITE_LOCKED = 6; private static final int SQLITE_IOERR = 10; private static final int SQLITE_CORRUPT = 11; private static final int SQLITE_NOTFOUND = 12; private static final int SQLITE_FULL = 13; private static final int SQLITE_CANTOPEN = 14; private static final int SQLITE_PROTOCOL = 15; private static final int SQLITE_TOOBIG = 18; private static final int SQLITE_CONSTRAINT = 19; private static final int SQLITE_MISMATCH = 20; private static final int SQLITE_NOTADB = 26; @Override public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() { return new SQLExceptionConversionDelegate() { @Override public JDBCException convert(SQLException sqlException, String message, String sql) { final int errorCode = JdbcExceptionHelper.extractErrorCode(sqlException) & 0xFF; if (errorCode == SQLITE_TOOBIG || errorCode == SQLITE_MISMATCH) { return new DataException(message, sqlException, sql); } else if (errorCode == SQLITE_BUSY || errorCode == SQLITE_LOCKED) { return new LockAcquisitionException(message, sqlException, sql); } else if ((errorCode >= SQLITE_IOERR && errorCode { if (info.getDatabaseName().equals("SQLite")) { return dialect; } return null; }; }package com.mindata.blockchain.core.sqlite.identity; import org.hibernate.dialect.Dialect; import org.hibernate.dialect.identity.IdentityColumnSupportImpl; /** * @author wuweifeng wrote on 2018/3/2. */ public class SQLiteDialectIdentityColumnSupport extends IdentityColumnSupportImpl { public SQLiteDialectIdentityColumnSupport(Dialect dialect) { super(dialect); } @Override public boolean supportsIdentityColumns() { return true; } /* public boolean supportsInsertSelectIdentity() { return true; // As specified in NHibernate dialect } */ @Override public boolean hasDataTypeInIdentityColumn() { // As specified in NHibernate dialect // FIXME true return false; } /* public String appendIdentitySelectToInsert(String insertString) { return new StringBuffer(insertString.length()+30). // As specified in NHibernate dialect append(insertString). append("; ").append(getIdentitySelectString()). toString(); } */ @Override public String getIdentitySelectString(String table, String column, int type) { return "select last_insert_rowid()"; } @Override public String getIdentityColumnString(int type) { // return "integer primary key autoincrement"; // FIXME "autoincrement" return "integer"; } }以上设置完毕就结束了。 然后你就可以在自己设置的model、Repository文件夹里添加自己的model类和Repository类，就和操作mysql一样，来享受Jpa带来的便捷吧。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |